package org.maggioni.springsandbox.rehearsals.e06jdbc.e02supportclasses.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.maggioni.springsandbox.rehearsals.e06jdbc.e02supportclasses.model.Job;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class JdbcDaoImpl extends JdbcDaoSupport {
	
	public int getJobCount() {
		return getJdbcTemplate().queryForInt("select count(*) from jobs");
	}

	public String getJobTitle(String jobId) {
		String sql = "select job_title from jobs where job_id=?";
		return getJdbcTemplate().queryForObject(sql, new Object[]{jobId}, String.class);
	}
	
	public Job getJob(String jobId) {
		String sql = "select job_id,job_title,min_salary,max_salary from jobs where job_id=?";
		return getJdbcTemplate().queryForObject(sql, new Object[]{jobId}, new JobMapper());
	}
	
	public List<Job> getAllJobs() {
		String sql = "select job_id,job_title,min_salary,max_salary from jobs";
		return getJdbcTemplate().query(sql, new JobMapper());
	}

	public void insertJob(Job job) {
		String sql = "insert into jobs (job_id,job_title,min_salary,max_salary) values (?,?,?,?)";
		getJdbcTemplate().update(sql, new Object[]{job.getJobId(),job.getJobTitle(),job.getMinSalary(),job.getMaxSalary()});
	}
	
	public void createATable() {
		boolean doRun = false;
		if (doRun){
			getJdbcTemplate().execute("create table prova (...)");
		}
	}


	private static final class JobMapper implements RowMapper<Job> {
		@Override
		public Job mapRow(ResultSet resultSet, int rowNum) throws SQLException {
			return new Job(
					resultSet.getString("job_id"),
					resultSet.getString("job_title"),
					resultSet.getInt("min_salary"),
					resultSet.getInt("max_salary")
					);
		}
	}
	
}
